﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;

namespace UniMana.Data
{
    public static class Training_TrainingProgramData
    {
        private static DBMain m_data = new DBMain();
        #region [Hữu]
        //Lấy danh sách theo khóa ngoại
        public static IQueryable GetListByRef(this Table<Training_TrainingProgram> nTrainingProgram, int _iLevelKey, int _iTraingTypeKey, int _iCourseKey, int _iMajorKey)
        {
            return nTrainingProgram.Where(p => p.Catalogue_LevelKey == _iLevelKey
                                                                    && p.Catalogue_TrainingTypeKey == _iTraingTypeKey
                                                                    && p.Catalogue_CourseKey == _iCourseKey
                                                                    && p.Catalogue_MajorKey == _iMajorKey
                                                                    && p.Catalogue_TrainingTypeKey == _iTraingTypeKey);
        }

        public static IQueryable GetListByKey(this Table<Training_TrainingProgram> nTrainingProgram, int _iTrainingProgramKey)
        {
            return nTrainingProgram.Where(p => p.Training_TrainingProgramKey == _iTrainingProgramKey);
        }

        public static IQueryable GetTrainingProgramByStudentID(this Table<Training_TrainingProgram> tProgram,
                                                Table<Training_Student_Class> tStudentClass, string studentID)
        {
            var listProgram = from p in tProgram
                              join
                                  sc in tStudentClass on p.Training_TrainingProgramKey equals sc.Training_TrainingProgramKey
                              where sc.ID == studentID
                              where sc.Mainly == true
                              select new
                                {
                                    sc.Training_TrainingProgramKey,
                                    p.ID,
                                    p.Name,
                                    p.Time,
                                    StudentID = sc.ID
                                };
            return listProgram;
        }
        #endregion

        #region [Hòa]
        public static bool CheckBeforDelete(this Table<Training_TrainingProgram> nTrainingProgram, Table<Training_TrainingProgram_Detail> nTrainingProgramDetail, int _iTrainingProgramKey)
        {
            var iQuery = from t in nTrainingProgram
                         join t0 in nTrainingProgramDetail on t.Training_TrainingProgramKey equals t0.Training_TrainingProgramKey
                         where
                           t.Training_TrainingProgramKey == _iTrainingProgramKey
                         select new
                         {
                             t0.Training_SubjectKey
                         };
            if (iQuery.Count() > 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion
        #region [An]
        /// <summary>
        /// Lấy mã, tên chương trình đào tạo
        /// </summary>
        /// <param name="nProgram"></param>
        /// <returns></returns>

        public static IQueryable GetData(this Table<Training_TrainingProgram> nTraining_TrainingProgram)
        {
            var values = from p in nTraining_TrainingProgram
                         select new
                         {
                             p.Training_TrainingProgramKey,
                             p.Name
                         };
            return values.Distinct();
        }

        /// <summary>
        /// Trả về chương trình đào tạo với loại hình, ngành, bậc, khóa
        /// </summary>
        /// <param name="nTraining_TrainingProgram"></param>
        /// <param name="m_iTrainingType"></param>
        /// <param name="m_iMajor"></param>
        /// <param name="m_iLevel"></param>
        /// <param name="m_iCourse"></param>
        /// <returns></returns>

        public static int ReturnTrainingProgramWithTrainingTypeMajorLevelCourse(this Table<Training_TrainingProgram> nTraining_TrainingProgram,
            int m_iTrainingType,
            int m_iMajor,
            int m_iLevel,
            int m_iCourse)
        {
            var values = from t in nTraining_TrainingProgram
                         where t.Catalogue_CourseKey == m_iCourse &&
                         t.Catalogue_LevelKey == m_iLevel &&
                         t.Catalogue_MajorKey == m_iMajor &&
                         t.Catalogue_TrainingTypeKey == m_iTrainingType
                         select new
                         {
                             t.Training_TrainingProgramKey
                         };
            return int.Parse(values.FirstOrDefault().Training_TrainingProgramKey.ToString());
        }
        #endregion

        #region [A.Đạt]
        /// <summary>
        /// Lấy danh sách cương trình đào tạo theo khóa ngoại
        /// Nếu không truyền khóa ngoại hàm bỏ đi điều kiện khóa ngoại
        /// </summary>
        /// <param name="_objLevelKey">Mã bậc đào tạo</param>
        /// <param name="_objTraingTypeKey">Mã loại</param>
        /// <param name="_objCourseKey">Mã khóa đào tạo</param>
        /// <param name="_objMajorKey">Mã chuyên ngành</param>
        /// <returns></returns>
        public static IQueryable GetListByRef(object _objLevelKey = null, object _objTraingTypeKey = null, object _objCourseKey = null, object _objMajorKey = null)
        {

            var IQuery = from p in m_data.m_DCReadOnly.Training_TrainingPrograms select p;
            if (_objLevelKey != null)
            {
                IQuery = IQuery.Where(p => p.Catalogue_LevelKey == int.Parse(_objLevelKey.ToString()));
            }

            if (_objTraingTypeKey != null)
            {
                IQuery = IQuery.Where(p => p.Catalogue_TrainingTypeKey == int.Parse(_objTraingTypeKey.ToString()));
            }

            if (_objCourseKey != null)
            {
                IQuery = IQuery.Where(p => p.Catalogue_CourseKey == int.Parse(_objCourseKey.ToString()));
            }

            if (_objMajorKey != null)
            {
                IQuery = IQuery.Where(p => p.Catalogue_MajorKey == int.Parse(_objMajorKey.ToString()));
            }

            return IQuery;
        }
        #endregion

        #region Chi --Hòa
        public static IQueryable GetListByMajorKey(
            this Table<Training_TrainingProgram> nTrainingProgram,
            int _iMajorKey)
        {
            var trainingProgram = nTrainingProgram.Where(
                p => p.Catalogue_MajorKey == _iMajorKey);
            return trainingProgram;
        }

        public static IQueryable GetListByMajorKeyCouserKey(
            this Table<Training_TrainingProgram> nTrainingProgram,
            int _iMajorKey,
            int _iCouserKey)
        {
            var trainingProgram = nTrainingProgram.Where(
                p => p.Catalogue_MajorKey == _iMajorKey &&
                    p.Catalogue_CourseKey == _iCouserKey);
            return trainingProgram;
        }


        public static IQueryable GetTrainingProgramByByRef(
            this Table<Training_TrainingProgram> nTrainingProgram,
            Table<Catalogue_Major> nMajor,
            Table<Catalogue_Department> nDepartment,
            int _iCouseKey,
            int _iDepartmentKey)
        {
            var iQuery = from t in nTrainingProgram
                         join t0 in nMajor
                            on t.Catalogue_MajorKey
                            equals t0.Catalogue_MajorKey
                         join t1 in nDepartment
                            on t0.Catalogue_DepartmentKey
                            equals t1.Catalogue_DepartmentKey
                         where
                           t1.Catalogue_DepartmentKey == _iDepartmentKey &&
                           t.Catalogue_CourseKey == _iCouseKey
                         select new
                         {
                             t.Training_TrainingProgramKey,
                             t.ID,
                             t.Name,
                             t1.Catalogue_DepartmentKey,
                             t.Catalogue_CourseKey
                         };
            return iQuery;
        }
        #endregion
    }
}
